﻿'Description:计划分解信息维护接口
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:YangSJ
'create Date:2017-12-6
'Rewriter:
'Rewrite Date:
Imports DatabaseAccess.DBUtility
Imports System.Data.OracleClient
Imports System.Text
Imports BusinessRule.DTO

Public Class uLMSPartPlanBusiness
    Implements BusinessRule.IBusiness.uLMSPartPlanIBusiness

    ''' <summary>
    ''' 判断同件号，同工艺，同大阶段,同订单是否有批次已经插入
    ''' </summary>
    ''' <param name="QueryCondition"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function CheckEngineHasInsert(ByVal QueryCondition As Dictionary(Of String, Object)) As Boolean Implements IBusiness.uLMSPartPlanIBusiness.CheckEngineHasInsert
        CheckEngineHasInsert = False
        Dim strsql As String = "SELECT * FROM enginepartplan ep  "
        strsql += String.Format("WHERE ep.productid='{0}' AND ep.workflowid='{1}' AND ep.mfgorderid='{2}' AND ep.workflowstepid='{3}'",
                              QueryCondition("ProductID"), QueryCondition("WorkflowID"), QueryCondition("MfgOrderID"), QueryCondition("WorkflowStepID"))
        If DatabaseHelper.Query_ReturnDatTable(strsql).Rows.Count > 0 Then
            CheckEngineHasInsert = True
        End If
    End Function

    ''' <summary>
    ''' Add by Yuxj20190328
    ''' 增加模板工艺及件号判断
    ''' </summary>
    ''' <param name="templeteName"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function CheckTempletWorkFlow(ByVal templeteName As String) As DataTable Implements IBusiness.uLMSPartPlanIBusiness.CheckTempletWorkFlow
        Dim sb As New StringBuilder
        Dim strsql As String =
            <string>                
                SELECT ep.productid pid,pb.revofrcdid rpid,pb.productname,p.productrevision pver,pp.productrevision rpver,wf.phasenode,
                 wf.workflowid wfid,wfb.workflowname,wfb.revofrcdid rwfid,wf.workflowrevision wfver,wff.workflowrevision rwfver FROM enginepartplan ep
                LEFT JOIN workflow wf ON ep.workflowid=wf.workflowid
                LEFT JOIN workflowbase wfb ON wf.workflowbaseid=wfb.workflowbaseid
                LEFT JOIN workflow wff ON wff.workflowid=wfb.revofrcdid
                LEFT JOIN product p ON ep.productid=p.productid
                LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid
                LEFT JOIN product pp ON pp.productid=pb.revofrcdid
            </string>
        sb.AppendLine(strsql)
        sb.AppendFormat(" WHERE ep.templetname='{0}' AND ( ep.workflowid<>wfb.revofrcdid) ", templeteName)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Return dt
    End Function

    ''' 保存计划分解数据
    ''' </summary>
    ''' <param name="QueryCondition"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SaveEnginePartPlanData(ByVal QueryCondition As Dictionary(Of String, Object)) As Boolean Implements IBusiness.uLMSPartPlanIBusiness.SaveEnginePartPlanData
        Dim objpara As Object()
        objpara = FillSaveEnginePartPlan(QueryCondition)
        Dim i = DatabaseHelper.ExecuteSql(objpara(0), objpara(1))
        If i > 0 Then
            SaveEnginePartPlanData = True
        Else
            SaveEnginePartPlanData = False
        End If
    End Function

    Public Function FillSaveEnginePartPlan(ByVal QueryCondition As Dictionary(Of String, Object)) As Object() Implements IBusiness.uLMSPartPlanIBusiness.FillSaveEnginePartPlan
        Dim strSql As String = <string>
                            INSERT INTO ENGINEPARTPLAN  (CONTAINERID,   CREATEDATE,   CYCLETIME,   EMPLOYEEID,   ENGINEPARTPLANID,  
                     MFGORDERID,   NOTE,   PLANNEDFINISHDATE,   PLANNEDSTARTDATE,   PRODUCTID,   WORKFLOWID,   
                     WORKFLOWSTEPID,   USECOMPLETED,containername,containerNO,templetname,CompleteStepId,WorkCenterId,isroutinetest,TYPE)
                    VALUES  (:CONTAINERID,   sysdate,   :CYCLETIME,   :EMPLOYEEID,   :ENGINEPARTPLANID,   :MFGORDERID,   :NOTE,   :PLANNEDFINISHDATE,
                       :PLANNEDSTARTDATE,   :PRODUCTID,   :WORKFLOWID,   :WORKFLOWSTEPID,   0,:containername,:containerno,:templetname,:CompleteStepId,:WorkCenterId,:isroutinetest,:TYPE)
    </string>

        Dim insertParam(17) As OracleParameter

        insertParam(0) = DatabaseHelper.NewParameter(":CONTAINERID", QueryCondition("ContainerID"), OracleType.VarChar)
        insertParam(1) = DatabaseHelper.NewParameter(":CYCLETIME", QueryCondition("CycleTime"), OracleType.Number)
        insertParam(2) = DatabaseHelper.NewParameter(":ENGINEPARTPLANID", Guid.NewGuid.ToString, OracleType.VarChar)
        insertParam(3) = DatabaseHelper.NewParameter(":EMPLOYEEID", QueryCondition("EmployeeID"), OracleType.VarChar)
        insertParam(4) = DatabaseHelper.NewParameter(":MFGORDERID", QueryCondition("MfgOrderID"), OracleType.VarChar)
        insertParam(5) = DatabaseHelper.NewParameter(":NOTE", QueryCondition("Note"), OracleType.VarChar)
        insertParam(6) = DatabaseHelper.NewParameter(":PLANNEDFINISHDATE", QueryCondition("PlannedFinishDate"), OracleType.DateTime)
        insertParam(7) = DatabaseHelper.NewParameter(":PLANNEDSTARTDATE", QueryCondition("PlannedStartDate"), OracleType.DateTime)
        insertParam(8) = DatabaseHelper.NewParameter(":PRODUCTID", QueryCondition("ProductID"), OracleType.VarChar)
        insertParam(9) = DatabaseHelper.NewParameter(":WORKFLOWID", QueryCondition("WorkflowID"), OracleType.VarChar)
        insertParam(10) = DatabaseHelper.NewParameter(":WORKFLOWSTEPID", QueryCondition("WorkflowStepID"), OracleType.VarChar)
        insertParam(11) = DatabaseHelper.NewParameter(":containername", QueryCondition("ContainerName"), OracleType.VarChar)
        insertParam(12) = DatabaseHelper.NewParameter(":containerno", QueryCondition("TOGETHERFLAG"), OracleType.VarChar)
        insertParam(13) = DatabaseHelper.NewParameter(":templetname", QueryCondition("TempletName"), OracleType.VarChar)

        insertParam(14) = DatabaseHelper.NewParameter(":CompleteStepId", QueryCondition("CompleteStepId"), OracleType.VarChar)
        insertParam(15) = DatabaseHelper.NewParameter(":WorkCenterId", QueryCondition("WorkCenterId"), OracleType.VarChar)
        If QueryCondition.Keys.Contains("IsRoutineTest") Then
            insertParam(16) = DatabaseHelper.NewParameter(":isroutinetest", QueryCondition("IsRoutineTest"), OracleType.Number)
        Else
            insertParam(16) = DatabaseHelper.NewParameter(":isroutinetest", 0, OracleType.Number)
        End If

        If QueryCondition.Keys.Contains("EnginePartPlanType") Then
            insertParam(17) = DatabaseHelper.NewParameter(":TYPE", QueryCondition("EnginePartPlanType"), OracleType.Number)
        Else
            insertParam(17) = DatabaseHelper.NewParameter(":TYPE", 1, OracleType.Number)
        End If
        Return New Object() {strSql, insertParam}
    End Function

    ''' 删除计划分解数据
    ''' Modify by Yuxj20190329 增加条件排除施工单
    ''' </summary>
    ''' <param name="QueryCondition"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function DeleteEnginePartPlanData(ByVal QueryCondition As System.Collections.Generic.Dictionary(Of String, Object)) As Boolean Implements IBusiness.uLMSPartPlanIBusiness.DeleteEnginePartPlanData
        Dim strSql As String = <string>
                            DELETE ENGINEPARTPLAN WHERE MFGORDERID= :MFGORDERID and  ep.workflowstepid IS NOT NULL
    </string>

        Dim insertParam(0) As OracleParameter

        insertParam(0) = DatabaseHelper.NewParameter(":MFGORDERID", QueryCondition("MfgOrderID"), OracleType.VarChar)
        Dim i = DatabaseHelper.ExecuteSql(strSql, insertParam)
        If i > 0 Then
            DeleteEnginePartPlanData = True
        Else
            DeleteEnginePartPlanData = False
        End If
    End Function
    ''' <summary>
    ''' 获取计划分解数据
    ''' </summary>
    ''' <param name="QueryCondition"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetEnginePartPlanData(ByVal QueryCondition As System.Collections.Generic.Dictionary(Of String, Object)) As System.Data.DataSet Implements IBusiness.uLMSPartPlanIBusiness.GetEnginePartPlanData
        Dim strSelect As String =
                    <string> SELECT E.CYCLETIME NORMALCYCLETIME,E.ENGINEPARTPLANID, E.MFGORDERID,E.NOTE,E.PLANNEDFINISHDATE,E.PLANNEDSTARTDATE, E.WORKFLOWSTEPID,
                       E.USECOMPLETED, WFS.WORKFLOWSTEPNAME  FROM ENGINEPARTPLAN E
                        LEFT JOIN WORKFLOWSTEP WFS    ON E.WORKFLOWSTEPID = WFS.WORKFLOWSTEPID 
                        INNER JOIN mfgorder m ON e.productid=m.productid AND e.mfgorderid=m.mfgorderid                                   
                   </string>
        Dim strCondition As New StringBuilder
        strCondition.AppendFormat("  WHERE E.MFGORDERID='{0}'", QueryCondition("MfgOrderID"))
        strSelect += strCondition.ToString
        strSelect += " ORDER BY WFS.SEQUENCE"
        Dim stepTable As DataTable = DatabaseHelper.Query_ReturnDatTable(strSelect)
        stepTable.TableName = "stepTable"
        strSelect =
                  <string> SELECT E.CYCLETIME, E.ENGINEPARTPLANID, E.MFGORDERID, E.NOTE, E.PLANNEDFINISHDATE, E.PLANNEDSTARTDATE,
                        E.WORKFLOWSTEPID,E.PRODUCTID, E.WORKFLOWID, WF.WORKFLOWREVISION, WFB.WORKFLOWNAME || ':' || WF.WORKFLOWREVISION WORKFLOW,
                        WFB.WORKFLOWNAME, E.USECOMPLETED, P.DESCRIPTION PRODUCTDESCRIPTION, PB.PRODUCTNAME, e.CONTAINERNAME,
                        e.containerno TOGETHERFLAG,C.CONTAINERID   FROM ENGINEPARTPLAN E
                         LEFT JOIN PRODUCT P ON E.PRODUCTID = P.PRODUCTID
                         LEFT JOIN PRODUCTBASE PB ON P.PRODUCTBASEID = PB.PRODUCTBASEID
                         LEFT JOIN CONTAINER C ON E.CONTAINERID = C.CONTAINERID
                         LEFT JOIN WORKFLOW WF ON E.WORKFLOWID = WF.WORKFLOWID
                         LEFT JOIN WORKFLOWBASE WFB ON WF.WORKFLOWBASEID = WFB.WORKFLOWBASEID
                        WHERE e.ContainerName is not null  
                       </string>
        strSelect += String.Format("  and E.MFGORDERID='{0}'", QueryCondition("MfgOrderID"))
        Dim containerTable As DataTable = DatabaseHelper.Query_ReturnDatTable(strSelect)
        containerTable.TableName = "containerTable"
        GetEnginePartPlanData = New DataSet
        GetEnginePartPlanData.Tables.Add(stepTable)
        GetEnginePartPlanData.Tables.Add(containerTable)
        GetEnginePartPlanData.Relations.Add(New DataRelation("stepid", stepTable.Columns("WORKFLOWSTEPID"), containerTable.Columns("WORKFLOWSTEPID")))

    End Function

    ''' <summary>
    ''' Add by Yuxj20180525 根据条件做分解删除
    ''' Modify by Yuxj20190329 增加 ep.workflowstepid IS NOT NULL排除施工单
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function DeleteEnginePartPlan(ByVal para As Dictionary(Of String, Object)) As Boolean Implements IBusiness.uLMSPartPlanIBusiness.DeleteEnginePartPlan
        Dim objpara As Object()
        objpara = FillDeleteEnginePartPlan(para)
        Dim i = DatabaseHelper.ExecuteSql(objpara(0))

        If i > 0 Then
            DeleteEnginePartPlan = True
        Else
            DeleteEnginePartPlan = False
        End If
    End Function

    Public Function FillDeleteEnginePartPlan(ByVal para As Dictionary(Of String, Object)) As Object() Implements IBusiness.uLMSPartPlanIBusiness.FillDeleteEnginePartPlan
        Dim sb As New StringBuilder
        sb.AppendLine(" delete from enginepartplan ep where ep.usecompleted='0'")
        If para.ContainsKey("MfgOrderId") Then
            sb.AppendFormat(" and mfgorderid='{0}'", para("MfgOrderId"))
        End If

        If para.ContainsKey("ContainerName") Then
            sb.AppendFormat(" and ContainerName='{0}'", para("ContainerName"))
        End If
        Return New Object() {sb.ToString, Nothing}
    End Function

    ''' <summary>
    ''' Add by YUxj20180521,判断部件批次是否在Container表开立批次
    ''' </summary>
    ''' <param name="containerName"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function boolEngineContainerExists(ByVal containerName As String) As Boolean Implements IBusiness.uLMSPartPlanIBusiness.boolEngineContainerExists
        Dim sb As New StringBuilder
        'sb.AppendLine("SELECT c.containername,c.containerid,c.mfgorderid FROM container c ")
        'sb.AppendLine("LEFT JOIN enginepartplan ep ON ep.containername=c.containername ")
        'sb.AppendFormat("WHERE ep.containername='{0}' ", containerName)
        sb.AppendFormat(<string>SELECT c.containername,c.containerid,c.mfgorderid FROM container c
        WHERE c.containername='{0}' </string>.Value, containerName)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        If dt.Rows.Count = 0 Then
            Return False
        Else
            Return True
        End If
    End Function

    ''' <summary>
    ''' Add by Yuxj20180521 执行更新操作
    ''' Modify by Yuxj20180604增加针对大阶段的更新，增加处理ContainerName为空的情况
    ''' </summary>
    ''' <param name="QueryCondition"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateEnginePartPlanData(ByVal QueryCondition As Dictionary(Of String, Object)) As Boolean Implements IBusiness.uLMSPartPlanIBusiness.UpdateEnginePartPlanData
        Dim objpara As Object()
        objpara = FillUpdateEnginePartPlan(QueryCondition)
        Dim i = DatabaseHelper.ExecuteSql(objpara(0), objpara(1))
        If i > 0 Then
            UpdateEnginePartPlanData = True
        Else
            UpdateEnginePartPlanData = False
        End If
    End Function

    Function FillUpdateEnginePartPlan(ByVal QueryCondition As Dictionary(Of String, Object)) As Object() Implements IBusiness.uLMSPartPlanIBusiness.FillUpdateEnginePartPlan
        Dim strSql As String = <string>
                                    update ENGINEPARTPLAN  set  CYCLETIME=:CYCLETIME,
                                    PLANNEDSTARTDATE=:PLANNEDSTARTDATE,
                                    PLANNEDFINISHDATE=:PLANNEDFINISHDATE,
                                    CompleteStepId=:CompleteStepId
                                </string>
        If QueryCondition.ContainsKey("EnginepartplanID") Then
            strSql += String.Format(" where EnginepartplanID='{0}'", QueryCondition("EnginepartplanID"))
        ElseIf QueryCondition("ContainerName") = String.Empty Then
            strSql += String.Format(" where workflowstepid='{0}' and mfgorderid='{1}' and containername is null ", QueryCondition("WorkflowStepID"), QueryCondition("MfgOrderID"))
        Else
            'Modify by Yuxj20180702
            If QueryCondition("WorkflowID") <> String.Empty Then
                strSql += String.Format(" ,workflowid='{0}' ", QueryCondition("WorkflowID"))
            End If
            strSql += String.Format(" where containername='{0}'", QueryCondition("ContainerName"))
        End If

        Dim insertParam(3) As OracleParameter
        insertParam(0) = DatabaseHelper.NewParameter(":CYCLETIME", QueryCondition("CycleTime"), OracleType.Double)
        insertParam(1) = DatabaseHelper.NewParameter(":PLANNEDFINISHDATE", QueryCondition("PlannedFinishDate"), OracleType.DateTime)
        insertParam(2) = DatabaseHelper.NewParameter(":PLANNEDSTARTDATE", QueryCondition("PlannedStartDate"), OracleType.DateTime)
        'Modify by Yuxj20181113
        insertParam(3) = DatabaseHelper.NewParameter(":CompleteStepId", QueryCondition("CompleteStepId"), OracleType.VarChar)
        Return New Object() {strSql, insertParam}
    End Function

    ''' <summary>
    ''' Add by Yuxj20180525 获取订单资料
    ''' </summary>
    ''' <param name="mfgid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetMfgData(ByVal mfgid As String) As DataTable Implements IBusiness.uLMSPartPlanIBusiness.GetMfgData
        Dim sb As New StringBuilder
        sb.AppendLine(" SELECT m.mfgordername,m.mfgorderid,m.factorycompletiondate,   ")
        sb.AppendLine(" m.factorymonth,m.factorystardate FROM mfgorder m ")
        sb.AppendFormat(" where mfgorderid='{0}'", mfgid)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Return dt
    End Function

    ''' <summary>
    ''' Add by Yuxj20180827
    ''' 炉头号与合格率DataTable行列转换方法
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function ConvertRCDataTable(ByVal dt As DataTable) As DataTable

        Dim rowCount As Integer = dt.Rows.Count + 1
        Dim columnsCount As Integer = dt.Columns.Count
        Dim newDt As DataTable = New DataTable()
        Dim dc As DataColumn = New DataColumn()

        For rowi As Integer = 0 To rowCount
            If (rowi = 0) Then
                If (dt.Columns(0).ToString() = "Name" Or dt.Columns(0).ToString() = "value") Then
                    dt.Columns("Name").ColumnName = "炉头号"
                    dt.Columns("value").ColumnName = "合格率"
                End If
                newDt.Columns.Add(dt.Columns(0).ToString())
            Else
                newDt.Columns.Add(dt.Rows(rowi - 1)(0).ToString())
            End If
        Next

        For columnsi As Integer = 0 To columnsCount
            Dim dr As DataRow = newDt.NewRow()
            For rowj As Integer = 0 To rowCount
                If (rowj = 0) Then
                    dr(rowj) = dt.Columns(columnsi).ToString()
                Else
                    dr(rowj) = dt.Rows(rowj - 1)(columnsi).ToString()
                End If
            Next

            If (columnsi <> 0) Then
                newDt.Rows.Add(dr)
            End If
        Next
        Return newDt

    End Function

#Region "订单维护页面更改 Yuxj20180131"
    ''' <summary>
    ''' 判断订单号对应的发动机号是否开卡
    ''' </summary>
    ''' <param name="mfgid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function ContainerHasExists(ByVal mfgid As String, ByRef Cid As String) As Boolean Implements IBusiness.uLMSPartPlanIBusiness.ContainerHasExists
        ContainerHasExists = True
        Dim sb As New StringBuilder
        sb.AppendFormat("select containerid from container c where c.mfgorderid='{0}'", mfgid)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        If dt.Rows.Count = 0 Then
            Cid = ""
            Return False
        Else
            Cid = dt.Rows(0)("containerid").ToString
        End If
    End Function

    ''' <summary>
    ''' 获取发动机件号本次开卡要产生的批次号
    ''' </summary>
    ''' <param name="pname"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetCNamebyPName(ByVal pname As String) As String Implements IBusiness.uLMSPartPlanIBusiness.GetCNamebyPName
        '发动机批次号编码规则 件号+/+Year(2位)
        Dim strYear As String = DateTime.Now.Year.ToString().Substring(2, 2)
        Dim sb As New StringBuilder
        sb.AppendFormat("SELECT Max(containername) MaxName FROM container c WHERE c.containername LIKE '{0}/{1}___'", pname, strYear)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        If dt.Rows.Count = 0 Then
            GetCNamebyPName = pname + "/" + strYear + "001"
        Else
            Dim strCName As String = dt.Rows(0)("MaxName").ToString
            If strCName.ToString.Trim = String.Empty Then
                strCName = pname + "/" + strYear + "001"
            Else
                Dim strFlowsn As Integer = Convert.ToInt32(strCName.Replace(pname + "/" + strYear, "").Trim)
                strCName = pname + "/" + strYear + (strFlowsn + 1).ToString.PadLeft(3, "0")
            End If
            GetCNamebyPName = strCName
        End If
    End Function

    ''' <summary>
    ''' 订单模板名称创建
    ''' Modify by Yuxj20180509将模板信息存在订单上，不单独存放，故修改删除模板的方式
    ''' </summary>
    ''' <param name="mfgorderId"></param>
    ''' <param name="NewTemplet"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function CreateMfgTemplet(ByVal mfgorderId As String, ByVal NewTemplet As String) As Boolean Implements IBusiness.uLMSPartPlanIBusiness.CreateMfgTemplet
        CreateMfgTemplet = False
        Dim strsql = String.Format("update enginepartplan ep set ep.templetname='{0}' where ep.mfgorderid='{1}'", NewTemplet, mfgorderId)
        DatabaseHelper.ExecuteSql(strsql)
        Return True
    End Function

    ''' <summary>
    ''' 订单模板名称修改
    ''' Modify by Yuxj20180509将模板信息存在订单上，不单独存放，故修改删除模板的方式
    ''' </summary>
    ''' <param name="NewTemplet"></param>
    ''' <param name="OldTemplet"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function updateMfgTemplet(ByVal NewTemplet As String, ByVal OldTemplet As String) As Boolean Implements IBusiness.uLMSPartPlanIBusiness.updateMfgTemplet
        updateMfgTemplet = False
        Dim strsql = String.Format("update enginepartplan ep set ep.templetname='{0}' where ep.templetname='{1}'", NewTemplet, OldTemplet)
        DatabaseHelper.ExecuteSql(strsql)
        Return True
    End Function
    Function UpdateMfgContainer(ByVal para As Dictionary(Of String, String)) As Boolean Implements IBusiness.uLMSPartPlanIBusiness.UpdateMfgContainer
        UpdateMfgContainer = False
        Dim sb As New StringBuilder
        sb.AppendLine(" update enginepartplan ep set ")
        If para.ContainsKey("WorkFlowId") Then
            sb.AppendFormat(" WorkFlowId='{0}'", para("WorkFlowId"))
            If para.ContainsKey("CycleTime") Then
                sb.AppendFormat(" ,cycletime='{0}'", para("CycleTime"))
                If para.ContainsKey("PlannedStartDate") Then
                    sb.AppendFormat(" ,plannedstartdate=to_date('{0}','YYYY/MM/DD')", para("PlannedStartDate"))
                End If
            Else
                If para.ContainsKey("PlannedStartDate") Then
                    sb.AppendFormat(" ,plannedstartdate=to_date('{0}','YYYY/MM/DD')", para("PlannedStartDate"))
                End If
            End If
        Else
            If para.ContainsKey("CycleTime") Then
                sb.AppendFormat(" cycletime='{0}'", para("CycleTime"))
                If para.ContainsKey("PlannedStartDate") Then
                    sb.AppendFormat(" ,plannedstartdate=to_date('{0}','YYYY/MM/DD')", para("PlannedStartDate"))
                End If
            Else
                If para.ContainsKey("PlannedStartDate") Then
                    sb.AppendFormat(" plannedstartdate=to_date('{0}','YYYY/MM/DD')", para("PlannedStartDate"))
                End If
            End If

        End If

        sb.AppendFormat(" where ep.containername='{0}' and ep.workflowstepid='{1}'", para("ContainerName"), para("WorkFlowStepId"))
        DatabaseHelper.ExecuteSql(sb.ToString)
        Return True
    End Function

    ''' <summary>
    ''' 获取大阶段对应产品Table Add by Yuxj20180515
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function StepWorkFlowProductDT(ByVal para As Dictionary(Of String, Object)) As DataTable Implements IBusiness.uLMSPartPlanIBusiness.StepWorkFlowProductDT
        Dim sb As New StringBuilder
        sb.AppendLine(" SELECT wf.workflowid,wf.workflowrevision,wfb.workflowname,p.productid,p.productrevision,pb.productname ")
        sb.AppendLine(" FROM workflow wf ")
        sb.AppendLine(" INNER JOIN workflowbase wfb ON wf.workflowbaseid=wfb.workflowbaseid ")
        sb.AppendLine(" INNER JOIN workflowproductfamilylist wfp ON wf.workflowid=wfp.workflowid")
        sb.AppendLine(" INNER JOIN productworkflowmapping pm ON wfp.workflowid=pm.workflowid")
        sb.AppendLine(" LEFT JOIN product p ON pm.productid=p.productid")
        sb.AppendLine(" LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid")
        sb.AppendFormat(" WHERE wf.phasenode='{0}' AND wfp.productfamilylistid='{1}' ", para("Step"), para("ProductFamilyId"))
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Return dt
    End Function

    ''' <summary>
    ''' 判断装试发动机大阶段是否有工艺路线Add by Yuxj20180514
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function StepWorkFlowDT(ByVal para As Dictionary(Of String, Object)) As DataTable Implements IBusiness.uLMSPartPlanIBusiness.StepWorkFlowDT
        Dim sb As New StringBuilder
        sb.AppendLine(" SELECT wf.workflowid,wf.workflowrevision,wfb.workflowname FROM workflow wf ")
        sb.AppendLine(" LEFT JOIN workflowbase wfb ON wf.workflowbaseid=wfb.workflowbaseid ")
        sb.AppendLine(" INNER JOIN workflowproductfamilylist wfp ON wf.workflowid=wfp.workflowid ")
        sb.AppendFormat(" WHERE wf.phasenode='{0}' AND wfp.productfamilylistid='{1}' ", para("Step"), para("ProductFamilyId"))
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Return dt
    End Function

    ''' <summary>
    ''' 获取产品工艺 Add by Yuxj20180514
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetProductWorkFlow(ByVal para As Dictionary(Of String, String)) As DataTable Implements IBusiness.uLMSPartPlanIBusiness.GetProductWorkFlow
        Dim EngineProductName As String = GetEngineProductName(para("MfgOrderId"))
        If EngineProductName = String.Empty Then
            Return New DataTable
            Exit Function
        End If
        Dim sb As New StringBuilder
        'Modify by Yuxj20180523工艺与版本分割符由竖杠改为:
        sb.AppendLine("  SELECT wf.workflowid,wfb.workflowname||':'||wf.workflowrevision workflownamever,wf.phasenode FROM")
        sb.AppendLine("  productworkflowmapping pw")
        'sb.AppendLine(" LEFT JOIN Workflowproductfamilylist wff ON pw.workflowid=wff.workflowid")
        sb.AppendLine(" INNER JOIN workflow wf ON pw.workflowid=wf.workflowid")
        sb.AppendFormat(" and instr(wf.ly2modle_node,'{0}')>0", EngineProductName)
        'Modify by Yuxj20180622 LEFT-->INNER
        sb.AppendLine(" INNER JOIN workflowbase wfb ON wf.workflowbaseid=wfb.workflowbaseid")
        'Modify by Yuxj20190111过滤空工艺
        sb.AppendLine(" INNER JOIN (SELECT DISTINCT wfs.workflowid FROM workflowstep wfs) wfs ON wfs.workflowid=wf.workflowid")
        sb.AppendFormat(" where pw.productid='{0}' ", para("Pid"))
        sb.AppendLine("  ORDER BY wfb.workflowname||'|'||wf.workflowrevision ASC")
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Return dt
    End Function

    Function GetProductWorkFlowCycletime(ByVal para As Dictionary(Of String, String)) As DataTable Implements IBusiness.uLMSPartPlanIBusiness.GetProductWorkFlowCycletime
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT DISTINCT ep.cycletime FROM enginepartplan ep ")
        sb.AppendLine("LEFT JOIN mfgorder m on ep.mfgorderid=m.mfgorderid ")
        sb.AppendLine(" LEFT JOIN workflowstep wfs ON ep.workflowstepid=wfs.workflowstepid ")
        sb.AppendLine("LEFT JOIN  product p ON ep.productid=p.productid ")
        sb.AppendLine("LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid ")
        sb.AppendLine("LEFT JOIN workflow wf ON ep.workflowid=wf.workflowid ")
        sb.AppendLine("LEFT JOIN Workflowbase wfb ON wf.workflowbaseid=wfb.workflowbaseid ")
        sb.AppendFormat("where ep.mfgorderid='{0}'  and nvl2(ep.workflowstepid,1,0)=1 ", para("MfgOrderId"))
        sb.AppendFormat(" AND ep.workflowstepid='{0}' ", para("WorkFlowStepId"))
        sb.AppendFormat("AND ep.WORKFLOWID <>'{0}' ", para("WorkFlowId"))
        sb.AppendFormat("AND ep.PRODUCTID <>'{0}'", para("Pid"))
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Return dt
    End Function

    ''' <summary>
    ''' Add by Yuxj20190111
    ''' 增加对ProductWorkflowMapping的判断
    ''' </summary>
    ''' <param name="para"></param>
    ''' <param name="EngineProductId"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function CheckProductWorkflowMapping(ByVal para As Dictionary(Of String, Object), ByVal EngineProductId As String) As Boolean Implements IBusiness.uLMSPartPlanIBusiness.CheckProductWorkflowMapping
        CheckProductWorkflowMapping = False
        If para("ProductID") = EngineProductId Then
            CheckProductWorkflowMapping = True
            Exit Function
        End If
        Dim sb As New StringBuilder
        sb.AppendFormat(<string>SELECT pw.*,wf.phasenode FROM productworkflowmapping pw
                                INNER JOIN workflow wf ON pw.workflowid=wf.workflowid
                                INNER JOIN (SELECT DISTINCT wfs.workflowid FROM workflowstep wfs) wfs ON wf.workflowid=wfs.workflowid
                                WHERE pw.workflowid='{0}' AND pw.productid='{1}' </string>.Value, para("WorkflowID"), para("ProductID"))
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        If dt.Rows.Count > 0 Then
            'Modify by Yuxj20190318 更改大阶段判断条件
            Dim phasenode As String = dt.Rows(0)("phasenode").ToString
            Dim WorkflowStepName As String = para("WorkflowStepName").ToString
            WorkflowStepName = WorkflowStepName.Substring(WorkflowStepName.IndexOf("-") + 1,
                                                          WorkflowStepName.Length - (WorkflowStepName.IndexOf("-") + 1))

            If phasenode.IndexOf(WorkflowStepName) >= 0 Then
                CheckProductWorkflowMapping = True
            End If

        End If
    End Function
    ''' <summary>
    ''' 判断模板信息 Modify by Yuxj20180520
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetMfgTemplet(ByVal para As Dictionary(Of String, String)) As DataSet Implements IBusiness.uLMSPartPlanIBusiness.GetMfgTemplet
        Dim ds As New DataSet
        Dim sb As New StringBuilder
        sb.AppendFormat("select ep.mfgorderid,ep.cycletime,ep.templetname,wfs.workflowstepid,ep.productid,ep.plannedstartdate,ep.plannedfinishdate from enginepartplan ep ")
        sb.AppendLine(" LEFT JOIN workflowstep wfs ON ep.workflowstepid=wfs.workflowstepid")
        'Modify by Yuxj20180917 增加条件如果订单被删除，对应模板不会显示
        sb.AppendLine(" INNER JOIN mfgorder m ON m.mfgorderid=ep.mfgorderid ")

        sb.AppendLine(" where ep.templetname is not null")
        If para.ContainsKey("TempletName") Then
            sb.AppendFormat(" and ep.templetname='{0}'", para("TempletName"))
        End If

        If para.ContainsKey("PId") Then
            sb.AppendFormat(" and ep.productid='{0}'", para("PId"))
        End If

        If para.ContainsKey("MfgOrderId") Then
            sb.AppendFormat(" and ep.MfgOrderId='{0}'", para("MfgOrderId"))
        End If

        sb.AppendLine(" order by ep.templetname asc,wfs.sequence ASC")
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        dt.TableName = "All"
        Dim dv As DataView = dt.DefaultView
        Dim dtTemplet As DataTable = dv.ToTable(True, "templetname")
        dtTemplet.TableName = "Templet"
        ds.Tables.Add(dt)
        ds.Tables.Add(dtTemplet)
        Return ds
    End Function

    ''' <summary>
    ''' 获取订单包含的批次
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetMfgContainer(ByVal para As Dictionary(Of String, String)) As DataTable Implements IBusiness.uLMSPartPlanIBusiness.GetMfgContainer
        Dim sb As New StringBuilder
        sb.AppendLine(" SELECT ep.workflowstepid, ep.containerid,ep.containerno,ep.containername ,ep.cycletime,ep.plannedstartdate,ep.plannedfinishdate,")
        sb.AppendLine(" pb.productname,p.description,p.productid,wfb.workflowname||'|'||wf.workflowrevision workflownamever")
        sb.AppendLine(" FROM enginepartplan ep")
        sb.AppendLine(" LEFT JOIN mfgorder m ON ep.mfgorderid=m.mfgorderid and ep.productid !=m.productid")
        sb.AppendLine(" LEFT JOIN product p ON ep.productid=p.productid")
        sb.AppendLine(" LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid")
        sb.AppendLine(" LEFT JOIN workflow wf ON ep.workflowid=wf.workflowid")
        sb.AppendLine(" LEFT JOIN Workflowbase wfb ON wf.workflowbaseid=wfb.workflowbaseid")

        'Modify by Yuxj20180510
        'sb.AppendLine(" where  ep.templetname IS NULL and ep.containerno is not null ")
        sb.AppendLine(" where  ep.containerno is not null ")
        If para.ContainsKey("MfgOrderName") Then
            sb.AppendFormat(" and m.mfgordername='{0}'", para("MfgOrderName"))
        End If

        If para.ContainsKey("WorkFlowStepId") Then
            sb.AppendFormat(" and ep.WorkFlowStepId='{0}'", para("WorkFlowStepId"))
        End If
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Return dt
    End Function

    ''' <summary>
    ''' Add by Yuxj20180525
    ''' 获取分解信息
    ''' </summary>
    ''' <param name="mfgid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function boolMfgOrderCreate(ByVal mfgid As String) As Boolean Implements IBusiness.uLMSPartPlanIBusiness.boolMfgOrderCreate
        Dim sb As New StringBuilder
        sb.AppendFormat(String.Format("SELECT * FROM enginepartplan ep WHERE ep.mfgorderid='{0}' and ep.workflowstepid is not null ", mfgid))
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        If dt.Rows.Count = 0 Then
            Return False
        Else
            Return True
        End If
    End Function

    ''' <summary>
    ''' 订单模板删除
    ''' Modify by Yuxj20180509将模板信息存在订单上，不单独存放，故修改删除模板的方式
    ''' </summary>
    ''' <param name="Templet"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function deleteMfgTemplet(ByVal Templet As String) As Boolean Implements IBusiness.uLMSPartPlanIBusiness.deleteMfgTemplet
        deleteMfgTemplet = False
        'Dim strsql = String.Format("delete from enginepartplan ep where ep.templetname='{0}'", Templet)
        Dim strsql = String.Format("update enginepartplan ep set ep.templetname=null where ep.templetname='{0}'", Templet)
        DatabaseHelper.ExecuteSql(strsql)
        Return True
    End Function

    ''' <summary>
    ''' 判断订单是否分解 Add by Yuxj20180525
    ''' </summary>
    ''' <param name="mfgOrderId"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function boolIsSplit(ByVal mfgOrderId As String) As Boolean Implements IBusiness.uLMSPartPlanIBusiness.boolIsSplit
        boolIsSplit = False
        Dim strsql As String = String.Format("select 1 from enginepartplan ep where ep.mfgorderid='{0}'", mfgOrderId)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql)
        If dt.Rows.Count > 0 Then
            Return True
        End If
    End Function

    ''' <summary>
    ''' Add by Yuxj20180515
    ''' 获取订单可以创建批次的缓存
    ''' Modify by Yuxj20180717
    ''' 增加查询工艺名称，一个件号多本工艺均生成批次号，但同一工艺不同版本的只保留已笔（默认为最新-revofrcdid对应版本的工艺）
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function SaveMfgOrderSession(ByVal para As Dictionary(Of String, String)) As DataSet Implements IBusiness.uLMSPartPlanIBusiness.SaveMfgOrderSession
        Dim ds As New DataSet
        Dim sb As New StringBuilder
        sb.AppendFormat(<string>
                SELECT ep.enginepartplanid,ep.mfgorderid,m.mfgordername, ep.containername,ep.containerno,
                ep.cycletime,ep.plannedstartdate ,ep.plannedfinishdate, ep.productid,pb.productname,
                p.productrevision,p.description,ep.workflowid,wfb.workflowname,wf.workflowrevision,
                wf.description wfdesc,  wfb.workflowname||':'||wf.workflowrevision workflownamever,
                 wfs.workflowstepid,wfs.workflowstepname,wfs.workflowid wfid,
                '1' existFlag,'0' addDel,'0' updateFlag, DECODE(wf.workflowid,wfb.revofrcdid,'1','0') wfdefault,
                  wf.phasenode ,DECODE(p.productid,pb.revofrcdid,'1','0') defaultFlag
                  FROM enginepartplan ep 
                  LEFT JOIN mfgorder m on ep.mfgorderid=m.mfgorderid 
                  LEFT JOIN workflowstep wfs ON ep.workflowstepid=wfs.workflowstepid 
                  LEFT JOIN  product p ON ep.productid=p.productid 
                  LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid 
                  LEFT JOIN workflow wf ON ep.workflowid=wf.workflowid  
                  LEFT JOIN Workflowbase wfb ON wf.workflowbaseid=wfb.workflowbaseid  
                  where ep.mfgorderid='{0}'  and nvl2(ep.workflowstepid,1,0)=1
                  ORDER BY wfs.sequence ASC </string>.Value, para("MfgOrderId"))

        '此为订单已经分解的批次信息
        '作用：1，直接作为大阶段Grid数据源 2，作为选择大阶段后，分解页面右边Grid中默认选中项
        Dim dtSplitAll As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString) '此订单的所有分解记录 add:Wangjh 20220517 添加注释
        dtSplitAll.TableName = "dtSplitAll"

        Dim dv As DataView = dtSplitAll.DefaultView
        dv.RowFilter = String.Format("productid='{0}' and containername is null", para("ProductId"))
        '获取已分解订单中大阶段没有工艺的部分
        Dim dtMfgNull As DataTable = dv.ToTable("dtMfgNull") '此订单所有分解记录中未 生成任务单 的 add:Wangjh 20220517 添加注释
        '订单大阶段Datatable
        Dim dtMfgStep As DataTable
        sb = New StringBuilder
        sb.AppendFormat(<string>
                SELECT wfb.workflowname,wf.workflowrevision,wf.assemblytype,0 CYCLETIME ,
                p.productrevision,pb.productname,wfs.sequence,wfs.workflowstepid,wfs.workflowstepname, 
                to_date('{0}','YYYY/MM/DD HH24:MI:SS') plannedstartdate,wfs.workflowid,
                to_date('{0}','YYYY/MM/DD HH24:MI:SS') plannedfinishdate,null containerno 
               FROM MFGORDER M 
               LEFT JOIN Userattribute u ON m.mfgorderid=u.parentid 
               LEFT JOIN PRODUCT P ON P.PRODUCTID='{1}'
               LEFT JOIN PRODUCTBASE PB ON PB.PRODUCTBASEID = p.PRODUCTBASEID 
               LEFT JOIN productworkflowmapping pw ON pw.productid=p.productid
               LEFT JOIN workflow wf ON wf.workflowid=pw.workflowid
               LEFT JOIN workflowbase wfb ON wf.workflowbaseid=wfb.workflowbaseid 
               LEFT JOIN workflowstep wfs ON wf.workflowid=wfs.workflowid 
               WHERE p.productid='{1}' AND u.userattributename='发动机' 
               and nvl(wf.phasenode,'0')='0' AND m.mfgorderid='{2}' and wf.workflowid='{3}'
               ORDER BY wfs.sequence ASC</string>.Value, para("StartDate"), para("ProductId"), para("MfgOrderId"), para("workflowId")) 'yuxj20221201

        dtMfgStep = DatabaseHelper.Query_ReturnDatTable(sb.ToString) '此订单的所有大阶段记录 add:Wangjh 20220517 添加注释
        Dim dtCheck = dtSplitAll.Clone
        '已分解的订单
        If dtSplitAll.Rows.Count > 0 Then
            For Each stepdr As DataRow In dtMfgStep.Rows
                Dim stepdrs = dtSplitAll.Select(String.Format("workflowstepid='{0}' and productid='{1}'", stepdr("workflowstepid"), para("ProductId")))
                If stepdrs.Length = 0 Then
                    Continue For
                End If
                stepdr("CYCLETIME") = stepdrs(0)("CYCLETIME")
                stepdr("plannedstartdate") = stepdrs(0)("plannedstartdate")
                stepdr("plannedfinishdate") = stepdrs(0)("plannedfinishdate")
            Next
            dtMfgStep.AcceptChanges() '此订单的所有大阶段记录,如果分解了，把时间周期追加上去 add:Wangjh 20220517 添加注释
            dtCheck = dtSplitAll.Select("containername is not null").CopyToDataTable '此订单已生成任务单的分解记录 add:Wangjh 20220517 添加注释
        End If
        dtMfgStep.TableName = "dtMfgStep"
        '获取本发动机大阶段需要开立批次的信息和不需要开立批次的阶段，作合并table
        sb = New StringBuilder

        '获取待分解订单没有工艺路线的大阶段批次==》对未分解的订单有效，已分解订单从分解记录表可以得到
        sb.AppendFormat(<string>
            SELECT null enginepartplanid, m.mfgorderid,m.mfgordername, null containername,  null containerno,
            to_number(null) cycletime, to_date(null) plannedstartdate, to_date(null) plannedfinishdate,
            p.productid,pb.productname,p.productrevision,p.description,wf.workflowid, wfb.workflowname, 
            wf.workflowrevision,wf.description wfdesc,wfb.workflowname||':'||wf.workflowrevision workflownamever,
           wfs.workflowstepid,wfs.workflowstepname,wfs.workflowid wfid,
             '0' existFlag,'0' addDel,'0' updateFlag,DECODE(wf.workflowid,wfb.revofrcdid,'1','0') wfdefault,
           wf.phasenode ,DECODE(p.productid,pb.revofrcdid,'1','0') defaultFlag
           FROM MFGORDER M 
           LEFT JOIN Userattribute u ON m.mfgorderid=u.parentid  
           LEFT JOIN PRODUCT P ON P.PRODUCTID='{0}' 
           LEFT JOIN PRODUCTBASE PB ON PB.PRODUCTBASEID = p.PRODUCTBASEID 
           LEFT JOIN productworkflowmapping pw ON pw.productid=p.productid
           LEFT JOIN workflow wf ON wf.workflowid=pw.workflowid
           LEFT JOIN Workflowbase wfb ON wf.workflowbaseid=wfb.workflowbaseid 
           LEFT JOIN workflowstep wfs ON wf.workflowid=wfs.workflowid  
           where  u.userattributename='发动机'  
           AND wf.phasenode is null AND p.productid='{0}' 
           AND m.mfgorderid='{1}' and wf.workflowid='{2}' </string>.Value, para("ProductId"), para("MfgOrderId"), para("workflowId")) 'yuxj20221201
        Dim dtNullSplit As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString) '此订单的所有大阶段记录,与dtMfgStep的列不一样 add:Wangjh 20220517 添加注释

        '获取订单大阶段中所有挂了工艺的批次Table
        sb = New StringBuilder
        With sb
            .AppendFormat(<string>
            SELECT null enginepartplanid, m.mfgorderid,m.mfgordername, null containername,  
            u.attributevalue||'-'||pb.productname containerno, to_number(null) cycletime,
            to_date(null) plannedstartdate ,to_date(null) plannedfinishdate, 
            p.productid, pb.productname, p.productrevision, p.description, wf.workflowid,wfb.workflowname, 
            wf.workflowrevision,wf.description wfdesc,wfb.workflowname||':'||wf.workflowrevision workflownamever, 
            wfs.workflowstepid,wfs.workflowstepname,wfs.workflowid wfid,
             '0' existFlag,'0' addDel,'0' updateFlag, DECODE(wf.workflowid,wfb.revofrcdid,'1','0') wfdefault,
             wf.phasenode ,DECODE(p.productid,pb.revofrcdid,'1','0') defaultFlag
            FROM MFGORDER M 
            LEFT JOIN Userattribute u ON m.mfgorderid=u.parentid 
            LEFT JOIN PRODUCT P1 ON P1.PRODUCTID='{0}' 
            LEFT JOIN productworkflowmapping pw ON pw.productid=p1.productid
            LEFT JOIN workflow wf1 ON wf1.workflowid=pw.workflowid--发动机件号工艺
            LEFT JOIN workflowstep wfs ON wf1.workflowid=wfs.workflowid  
            LEFT JOIN workflow wf on INSTR(wf.phasenode,SUBSTR(wfs.workflowstepname,INSTR(wfs.workflowstepname,'-')+1))>0 --所有关在此大阶段的零部件工艺
  left join (select count(wfs1.workflowstepid) countids,wfs1.workflowid from workflowstep wfs1
            group by wfs1.workflowid) wfs1 on wfs1.workflowid=wf.workflowid
            LEFT JOIN Workflowbase wfb ON wf.workflowbaseid=wfb.workflowbaseid 

            INNER JOIN (SELECT pm.workflowid,MAX(pm.productid) productid  FROM productworkflowmapping pm 
                GROUP BY pm.workflowid) A ON A.WORKFLOWID=WF.WORKFLOWID 
            LEFT JOIN  product p ON a.productid=p.productid 
            LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid--零部件工艺挂的零部件 
</string>.Value, para("ProductId"))
            If dtSplitAll.Rows.Count = 0 Then
                .AppendLine(" inner JOIN productbase pbv ON a.productid=pbv.REVOFRCDID--零部件默认版本 ")
            End If
            .AppendFormat("where p1.productid='{0}'  AND u.userattributename='发动机' and wfs1.countids>0 and wf1.phasenode is null ", para("ProductId"))
            If para.ContainsKey("ProductName") Then
                .AppendFormat(" AND (wf.ly2modle_node LIKE '%;{0};%' OR  wf.ly2modle_node LIKE '{0};%' OR wf.ly2modle_node LIKE '%;{0}' OR wf.ly2modle_node='{0}') ", para("ProductName"))
            End If
            .AppendFormat("AND m.mfgorderid='{0}' ", para("MfgOrderId"))
            .AppendFormat(" AND pw.workflowid='{0}'", para("workflowId")) 'yuxj2022大阶段工艺
        End With
        Dim dtNotNullSplit As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString) '此订单大阶段下所有的零部件记录,未分解只查默认件号版本 add:Wangjh 20220517 添加注释

        'Modify by Yuxj20181124 因为采用了数据库查询的新递归语句，极大提高查询效率，此处方法简化
        Dim FPName As String = para("ProductName")
        'yuxj20230224取消和整机bom比对 总体刘榕洲提出
        'Dim dtWholeBom As DataTable = GetEngineWholeBom(FPName)
        Dim strTmpProduct As String = String.Empty
        Dim drsWhole As DataRow()

        'Modify by Yuxj20181124通过筛选bom没有的productname
        For Each drW As DataRow In dtNotNullSplit.DefaultView.ToTable(True, "productname").Rows
            If drW("productname") = FPName Then
                Continue For
            End If

            'yuxj20230224取消和整机bom比对 总体刘榕洲提出
            'drsWhole = dtWholeBom.Select(String.Format("idnrk='{0}'", drW("productname")))
            'If drsWhole.Length = 0 Then
            '    drsWhole = dtNotNullSplit.Select(String.Format("productname='{0}'", drW("productname")))
            '    For Each dr As DataRow In drsWhole
            '        dr.Delete()
            '    Next
            'End If
        Next
        dtNotNullSplit.AcceptChanges() '将整机bom中没有的零部件记录删掉 add:Wangjh 20220517 添加注释
        '通过筛选已有的workflowid,workflowstepid
        For Each drW As DataRow In dtNotNullSplit.DefaultView.ToTable(True, "workflowid", "workflowstepid").Rows

            drsWhole = dtCheck.Select(String.Format("workflowid='{0}' and workflowstepid='{1}'", drW("workflowid"), drW("workflowstepid")))
            For Each dr As DataRow In drsWhole
                    dr.Delete()
            Next
        Next
        dtCheck.AcceptChanges() '在已生成任务单的记录中，又删掉了所有的零部件记录？没看懂 add:Wangjh 20220517 添加注释

        'Modify by Yuxj20180702 增加试车工艺处理
        '试车大阶段包含工厂试车和检验试车，二者同一本工艺，在发动机件号和工艺关联关系的表
        '没有资料productworkflowmapping，试车工艺在Workflow 表ly2modle_node没有记录发动机件号
        '判断发动机大阶段是否包含工厂试车和检验试车，如果存在，和试车工艺关联生成阶段任务单信息存入
        '对已经分解的订单，如果有工厂和检验试车，但没有对应任务单，不做处理==>有可能更改周期，故同样需要处理
        '对未分解订单，如有工厂和检验试车，生成对应任务单信息，件号取发动机件号

        Dim dtMerge As New DataTable
        'Modify by Yuxj20181122 一个件号两个版本，对应同一本工艺，如果为已分解订单，取分解时的记录
        '如果为未分解订单 取默认工艺
        If dtSplitAll.Rows.Count > 0 Then
            dtMfgNull.Merge(dtNotNullSplit)
            dtMerge = dtMfgNull.Copy
            dtMerge.Merge(dtCheck)
        Else
            dtNullSplit.Merge(dtNotNullSplit)
            dtMerge = dtNullSplit.Copy
        End If
        'Modify by Yuxj20180705 此处存在的问题，如果存在试车工艺，则合并后的table中
        '工厂试车和检验试车大阶段会分别出现有ContainerNO和ContainerNo=null的两笔记录，后面需删除

        '对于已经分解过的订单，执行将已经分解的记录dtContainer填充到dtMerge中
        Dim drsAll As DataRow()
        Dim drAll As DataRow
        Dim WfsId As String = String.Empty, Wfid As String = String.Empty, Pid As String = String.Empty, Pname As String = String.Empty
        For Each drM As DataRow In dtMerge.Rows
            If dtSplitAll.Rows.Count = 0 Then
                Exit For
            End If

            '根据WorkflowstepId,Productid,workflowid到dtSplitAll查找并回填
            WfsId = drM("workflowstepid").ToString
            Wfid = drM("workflowid").ToString
            Pid = drM("Productid").ToString

            drsAll = dtSplitAll.Select(String.Format("workflowstepid='{0}' and workflowid='{1}' and Productid='{2}' and containername is not null",
                                                WfsId, Wfid, Pid))
            If drsAll.Length = 0 Then
                Continue For
            Else
                drAll = drsAll(0)
                drM("containername") = drAll("containername")
                drM("containerno") = drAll("containerno")
                drM("existFlag") = drAll("existFlag")
                drM("cycletime") = drAll("cycletime")
                drM("plannedstartdate") = drAll("plannedstartdate")
                drM("plannedfinishdate") = drAll("plannedfinishdate")
                drM("enginepartplanid") = drAll("enginepartplanid")
            End If
        Next

        'Add by Yuxj20180518,如果一个件号在同一阶段有两个工艺，则合并成的表中会出现一个阶段一个件号有两笔的情况
        'Modify by Yuxj20180702如果件号有检验工艺，不用删除
        '此处操作需删掉一笔，只能删除没有批次号的那一笔for each --next 操作不能对记录中作行删除
        '排序
        dv = dtMerge.DefaultView
        'Modify by Yuxj20180702 增加phaseNode排序
        dv.Sort = "workflowstepid,productname,workflowname,workflowid,Productid,phaseNode,existFlag,wfdefault,containername,defaultFlag asc"
        dtMerge = dv.ToTable("dtMerge")

        WfsId = String.Empty
        Wfid = String.Empty
        Pid = String.Empty
        Pname = String.Empty
        Dim WfName As String = String.Empty

        For i As Integer = dtMerge.Rows.Count - 1 To 0 Step -1
            'Modify by Yuxj20180702 针对部装有两本工艺，一本挂部装，一本挂部装;试验，且两本都需保留的情况 增加PhaseNode的判断
            'Modify by Yuxj20180717 修改逻辑，同一件号不同工艺名称都创建记录，相同工艺名称只保留一笔（首先显示默认工艺）

            'Modify by Yuxj20190329  And (Pid = dtMerge.Rows(i)("Productid").ToString=> And (Pid = dtMerge.Rows(i)("Productid").ToString)
            If (WfsId = dtMerge.Rows(i)("workflowstepid").ToString) And (Pname = dtMerge.Rows(i)("Productname").ToString) And
                      (WfName = dtMerge.Rows(i)("workflowname").ToString) Then 
	    	'Modify by Yuxj20180718,对分解过的订单和未分解的订单做不同处理
                '分解过的订单取已经分解的记录, 如果分解过的订单某件号原来没选，取默认工艺
                '未分解的订单同一件号，同一大阶段，同一工艺名称只取默认版本一笔
                If String.IsNullOrWhiteSpace(dtMerge.Rows(i)("containername").ToString) Then
                    dtMerge.Rows.Remove(dtMerge.Rows(i))
                    Continue For
                End If
            End If
            WfsId = dtMerge.Rows(i)("workflowstepid").ToString
            Wfid = dtMerge.Rows(i)("workflowid").ToString
            Pid = dtMerge.Rows(i)("Productid").ToString
            WfName = dtMerge.Rows(i)("workflowname").ToString
            Pname = dtMerge.Rows(i)("Productname").ToString
        Next

        ds.Tables.Add(dtSplitAll)
        ds.Tables.Add(dtMfgStep)
        ds.Tables.Add(dtMerge)
        Return ds

    End Function

    ''' <summary>
    ''' 判断是否为大阶段工艺Add by Yuxj20180520
    ''' </summary>
    ''' <param name="stepid"></param>
    ''' <param name="workflowid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function boolStepidInWorkflow(ByVal workflowid, ByVal stepid) As Boolean Implements IBusiness.uLMSPartPlanIBusiness.boolStepidInWorkflow
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT wfs.workflowid,wfs.workflowstepid,wfs.workflowstepname FROM workflowstep wfs  ")
        sb.AppendFormat("Where wfs.workflowid='{0}' AND wfs.workflowstepid='{1}' ", workflowid, stepid)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        If dt.Rows.Count > 0 Then
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' Add by Yuxj20180520 根据订单获取发动机号
    ''' </summary>
    ''' <param name="mfgid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetEngineNo(ByVal mfgid As String) As String Implements IBusiness.uLMSPartPlanIBusiness.GetEngineNo
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT u.attributevalue FROM Userattribute u ")
        sb.AppendLine("LEFT JOIN mfgorder m ON u.parentid=m.mfgorderid ")
        sb.AppendFormat("WHERE u.userattributename='发动机' and m.mfgorderid='{0}' ", mfgid)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        If dt.Rows.Count > 0 Then
            Return dt.Rows(0)("attributevalue").ToString
        Else
            Return ""
        End If
    End Function

    ''' <summary>
    ''' 根据工艺获取BomId
    ''' </summary>
    ''' <param name="workflowid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetBomListbyWorkflowid(ByVal workflowid As String) As DataTable Implements IBusiness.uLMSPartPlanIBusiness.GetBomListbyWorkflowid
        Dim sql As String =
            <string>
                SELECT  b.bomid,bb.bomname,b.bomrevision,p.productid childproductid,
                        pb.productname CHILDproductname,p.description CHILDdescription FROM bom b
                        LEFT JOIN bombase bb ON b.bombaseid=bb.bombaseid
                        LEFT JOIN productmateriallistitem pm ON b.bomid=pm.bomid
                        LEFT JOIN product p ON pm.productid=p.productid
                        LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid
                        inner JOIN productworkflowmapping pw ON b.workflowid=pw.workflowid
                        LEFT JOIN product p1 ON pw.productid=p.workflowid       
                        LEFT JOIN productbase pb1 ON p1.productbaseid=pb1.productbaseid    
            </string>
        sql += String.Format("WHERE b.workflowid='{0}' ", workflowid)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sql)
        Return dt
    End Function

    ''' <summary>
    ''' 更改订单完成时间Add by Yuxj20180520
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function FillUpdateMfgFinishDate(ByVal para As Dictionary(Of String, Object)) As Object() Implements IBusiness.uLMSPartPlanIBusiness.FillUpdateMfgFinishDate
        Dim sb As New StringBuilder
        sb.AppendLine("update mfgorder m set ")
        sb.AppendFormat("m.factorycompletiondate=to_date('{0}','YYYY/MM/DD HH24:MI:SS') where m.mfgorderid='{1}' ", para("PlannedStartDate"), para("MfgOrderID"))
        Return New Object() {sb.ToString, Nothing}

    End Function

    Function UpdateMfgFinishDate(ByVal para As Dictionary(Of String, Object)) As Boolean Implements IBusiness.uLMSPartPlanIBusiness.UpdateMfgFinishDate
        Dim objpara As Object()
        objpara = FillUpdateMfgFinishDate(para)
        DatabaseHelper.ExecuteSql(objpara.First)
        Return True
    End Function
    ''' <summary>
    ''' Add by Yuxj20180605 获取发动机件号
    ''' </summary>
    ''' <param name="mfgid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetEngineProductName(ByVal mfgid As String) As String Implements IBusiness.uLMSPartPlanIBusiness.GetEngineProductName
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT PB.PRODUCTNAME FROM MFGORDER T")
        sb.AppendLine("LEFT JOIN PRODUCTBASE PB1  ON PB1.PRODUCTBASEID = T.PRODUCTBASEID")
        sb.AppendLine("LEFT JOIN PRODUCT P1  ON P1.PRODUCTID = T.PRODUCTID")
        sb.AppendLine("LEFT JOIN PRODUCT P  ON P.PRODUCTID = NVL(PB1.REVOFRCDID, T.PRODUCTID) ")
        sb.AppendLine("LEFT JOIN PRODUCTBASE PB  ON PB.PRODUCTBASEID = NVL(P1.PRODUCTBASEID, T.PRODUCTBASEID) ")
        sb.AppendFormat("where t.mfgorderid='{0}'", mfgid)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        If dt.Rows.Count = 0 Then
            Return String.Empty
        Else
            Return dt.Rows(0)("PRODUCTNAME")
        End If
    End Function

    ''' <summary>
    ''' 获取临时订单资料列表
    ''' Modify by Yuxj20190327,增加函数GET_SPLIT_MFGORDER_PRODUCTID获取订单Productid
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetTempOrderTable(ByVal para As Dictionary(Of String, String)) As DataTable Implements IBusiness.uLMSPartPlanIBusiness.GetTempOrderTable
        Dim sb As New StringBuilder
        Dim strsql As String =
            <string>
                SELECT m.mfgordername,m.mfgorderid,ot.ordertypename,u.attributevalue,wf.ly2modle_node,wfb.workflowname,
                wf.workflowid,wf.workflowrevision,p.productrevision,
                p.productid,pb1.productname,m.factorystardate,m.factorycompletiondate
                FROM mfgorder m
                --LEFT JOIN productbase pb ON m.productbaseid=pb.productbaseid
                LEFT JOIN product p ON p.productid=GET_SPLIT_MFGORDER_PRODUCTID(m.mfgorderid) --NVL(pb.revofrcdid,m.productid)
                LEFT JOIN productbase pb1 ON p.productbaseid=pb1.productbaseid
                LEFT JOIN Ordertype ot ON m.ordertypeid=ot.ordertypeid
                LEFT JOIN Userattribute u ON m.mfgorderid=u.parentid AND u.userattributename='发动机'
                LEFT JOIN workflow wf ON u.attributevalue=wf.ly2modle_node
                LEFT JOIN workflowbase wfb ON wf.workflowbaseid=wfb.workflowbaseid
                WHERE 
                --ot.ordertypename in ('PD05','PD06','PD11','PD13','PD14') AND
                -- wf.workflowid NOT IN (SELECT DISTINCT workflowid FROM enginepartplan ep)
                1=1 and Not EXISTS (SELECT 1 FROM enginepartplan ep WHERE ep.workflowid=wf.workflowid) 
            </string>
        With sb
            .AppendLine(strsql)
            If para.ContainsKey("MfgOrderList") Then
                .AppendFormat("  AND m.mfgorderid in ('{0}')  ",
                          para("MfgOrderList").Substring(0, para("MfgOrderList").Length - 1).Replace(",", "','"))
            End If

            If para.ContainsKey("WorkFlowName") Then
                .AppendFormat("  AND wfb.WorkFlowName in ('{0}')  ",
                              para("WorkFlowName").Substring(0, para("WorkFlowName").Length - 1).Replace(",", "','"))
            End If

        End With
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Return dt
    End Function

    ''' <summary>
    ''' Add by Yuxj20180703,判断试车工艺是否存在
    ''' </summary>
    ''' <param name="workflowcheck"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetWorkFlowCheck(ByVal workflowcheck As String) As DataTable Implements IBusiness.uLMSPartPlanIBusiness.GetWorkFlowCheck
        Dim sb As New StringBuilder
        With sb
            .AppendLine("  SELECT wf.* FROM workflow wf ")
            .AppendLine("   INNER JOIN workflowbase wfb ON wf.workflowbaseid=wfb.workflowbaseid ")
            .AppendFormat("  WHERE wfb.workflowname='{0}' ", workflowcheck)
        End With
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Return dt
    End Function

    ''' <summary>
    ''' Add by Yuxj20180706
    ''' 获取件号的下一级Bom
    ''' </summary>
    ''' <param name="FPName"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetEngineBomTable(ByVal FPName As String) As DataTable Implements IBusiness.uLMSPartPlanIBusiness.GetEngineBomTable
        Dim sb As New StringBuilder
        With sb
            .AppendLine("  SELECT MATNR,IDNRK FROM mbomtable_tmp  ")
            .AppendFormat("  WHERE MATNR='{0}'  AND IDNRK<>'{0}'", FPName)
        End With
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Return dt
    End Function

    ''' <summary>
    ''' Add by Yuxj20180706 获取件号的Bom列表
    ''' 通过获取整机Bom中所有数据，通过数据库表递归获取发动机件号整机Bom的方法 Modify by Yuxj20181124 (弃用)
    ''' 多次访问数据库，导致效率较差
    ''' </summary>
    ''' <param name="FPName"></param>
    ''' <param name="dtRes"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetEngineBomTree(ByVal FPName As String, ByRef dtRes As DataTable) As DataTable Implements IBusiness.uLMSPartPlanIBusiness.GetEngineBomTree
        GetEngineBomTree = dtRes
        Dim dt As DataTable = GetEngineBomTable(FPName)
        If dt.Rows.Count = 0 Then
            Exit Function
        End If

        If dtRes.Rows.Count > 0 Then
            '验证bom嵌套问题 Yangsj 2018-11-20
            For Each dr As DataRow In dt.Rows
                If dtRes.Select(String.Format("MATNR='{0}' AND IDNRK ='{1}'", dr("MATNR"), dr("IDNRK"))).Length > 0 Then
                    dr.Delete()
                Else
                    dtRes.Rows.Add(dr.ItemArray)
                End If
            Next
        Else
            dtRes.Merge(dt)
        End If

        For Each dr As DataRow In dt.Rows
            If dr.RowState = DataRowState.Deleted Then
                Continue For
            End If
            FPName = dr("idnrk")
            GetEngineBomTree(FPName, dtRes)
        Next
    End Function

    ''' <summary>
    ''' 获取所有发动机Bom列表 Add by Yuxj20180731
    ''' 通过数据库提供的递归Sql查询整机Bom Modify by Yuxj20181124 
    ''' Modify by Yuxj20190103 添加nocycle
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetEngineWholeBom(ByVal FPName As String) As DataTable Implements IBusiness.uLMSPartPlanIBusiness.GetEngineWholeBom
        GetEngineWholeBom = New DataTable
        Dim sb As New StringBuilder
        With sb
            .AppendLine("  SELECT MATNR,IDNRK FROM mbomtable_tmp m WHERE MATNR<> IDNRK")
            .AppendFormat(" start with m.matnr = '{0}' ", FPName)
            .AppendLine(" connect by nocycle prior m.idnrk= m.matnr ")
        End With
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        GetEngineWholeBom = dt
    End Function

    ''' <summary>
    ''' 获取单个发动机Bom Add by Yuxj20180731
    ''' 通过获取整机Bom中所有数据，通过Datetable递归获取发动机件号整机Bom的方法 Modify by Yuxj20181124 (弃用)
    ''' </summary>
    ''' <param name="dt"></param>
    ''' <param name="FPName"></param>
    ''' <param name="dtRes"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetEngineBomList(ByVal dt As DataTable, ByVal FPName As String, ByRef dtRes As DataTable) As DataTable Implements IBusiness.uLMSPartPlanIBusiness.GetEngineBomList
        'Modify by Yuxj20180731如果发动机总表为空，则推出
        If dt.Rows.Count = 0 Then
            GetEngineBomList = New DataTable
            Exit Function
        End If
        Dim drs As DataRow() = dt.Select(String.Format("MATNR='{0}' AND IDNRK <>'{0}'", FPName))
        If drs.Length > 0 Then
            '验证bom嵌套问题 Yangsj 2018-11-20
            If dtRes.Rows.Count > 0 Then
                For i = 0 To drs.Length - 1
                    If dtRes.Select(String.Format("MATNR='{0}' AND IDNRK ='{1}'", drs(i)("MATNR"), drs(i)("IDNRK"))).Length > 0 Then
                        drs(i).Delete()
                    Else
                        dtRes.Rows.Add(drs(i).ItemArray)
                    End If
                Next
            Else
                dtRes.Merge(drs.CopyToDataTable)
            End If

            For i = 0 To drs.Length - 1
                If drs(i).RowState = DataRowState.Deleted Then
                    Continue For
                End If
                GetEngineBomList(dt, drs(i)("IDNRK"), dtRes)
            Next
        End If
        GetEngineBomList = dtRes
    End Function

    ''' <summary>
    ''' Add by Yuxj20180927
    ''' 更加订单分解表Id获取任务对于的工序列表
    ''' </summary>
    ''' <param name="EnginePartPlanId"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetWorkFlowStepList(ByVal EnginePartPlanId As String) As DataTable Implements IBusiness.uLMSPartPlanIBusiness.GetWorkFlowStepList
        Dim sb As New StringBuilder
        With sb
            .AppendLine(" SELECT wfs.workflowstepid,wfs.workflowstepname FROM enginepartplan ep  ")
            .AppendLine(" LEFT JOIN workflowstep wfs ON ep.workflowid=wfs.workflowid ")
            .AppendFormat(" WHERE ep.enginepartplanid='{0}' ", EnginePartPlanId)
            .AppendLine(" ORDER BY wfs.sequence ASC")
        End With
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Return dt
    End Function

    ''' <summary>
    ''' Add by Yuxj20180927
    ''' 更新发动机部件完工工序
    ''' Modify by Yuxj20181113 批量更新
    ''' </summary>
    ''' <param name="EnginePartPlanId"></param>
    ''' <param name="CompleteStepId"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateCompleteStepid(ByVal EnginePartPlanId As String, ByVal CompleteStepId As String) As Boolean Implements IBusiness.uLMSPartPlanIBusiness.UpdateCompleteStepid
        Dim sb As New StringBuilder
        With sb
            .AppendLine(" update enginepartplan ep  ")
            .AppendFormat(" set CompleteStepId='{0}' ", CompleteStepId)
            .AppendFormat(" WHERE ep.enginepartplanid='{0}' ", EnginePartPlanId)
        End With
        Dim row As Integer = DatabaseHelper.ExecuteSql(sb.ToString)
        If row > 0 Then
            sb = New StringBuilder
            With sb
                .AppendFormat(" select workflowid ,productid from enginepartplan ep where ep.enginepartplanid='{0}'  ", EnginePartPlanId)
            End With
            Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
            If dt.Rows.Count > 0 Then
                sb = New StringBuilder
                With sb
                    .AppendLine(" update enginepartplan ep ")
                    .AppendFormat(" set CompleteStepId='{0}' ", CompleteStepId)
                    .AppendFormat(" where workflowid='{0}' and productid='{1}' and usecompleted='0' and CompleteStepId is null",
                                  dt.Rows(0)("workflowid").ToString, dt.Rows(0)("productid").ToString)
                End With
                DatabaseHelper.ExecuteSql(sb.ToString)
            End If


            Return True
        Else
            Return False
        End If

    End Function

    ''' <summary>
    ''' Add by Yuxj20180927
    ''' 判断是否为完工工序
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function boolCompleteStep(ByVal para As Dictionary(Of String, String)) As Boolean Implements IBusiness.uLMSPartPlanIBusiness.boolCompleteStep
        boolCompleteStep = False
        Dim strsql = String.Format(" SELECT *FROM enginepartplan ep WHERE ep.containerid='{0}' ", para("ContainerId"))
        'Modify by Yuxj20181217
        If para.ContainsKey("StepId") Then
            strsql += String.Format("AND ep.completestepid='{0}' ", para("StepId"))
        Else
            strsql += " and ep.completestepid is not null "
        End If
        If DatabaseHelper.Query_ReturnDatTable(strsql).Rows.Count > 0 Then
            Return True
        End If
    End Function

    ''' <summary>
    ''' Add by Yuxj20180927
    ''' 判断是否为工序的最后一道工步
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function boolLastStepinfo(ByVal para As Dictionary(Of String, String)) As Boolean Implements IBusiness.uLMSPartPlanIBusiness.boolLastStepinfo
        boolLastStepinfo = False
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT si.* FROM stepinfo SI ")
        sb.AppendFormat(" where si.workflowstepid='{0}' ", para("StepId"))
        sb.AppendLine(" ORDER BY si.Stepno DESC")
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        If dt.Rows.Count > 0 Then
            If dt.Rows(0)("stepinfoid").ToString = para("StepInfoId") Then
                Return True
            End If
        End If
    End Function

    ''' <summary>
    ''' Add by Yuxj20180927更新完工标记
    ''' Modify by Yuxj20181016记录完工时间ep.finishdate
    ''' Modify by Yuxj20181204
    ''' 增加大阶段完工判断，如果一个任务单所有大阶段都完工，将发动机大阶段完工   
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateUseComplete(ByVal para As Dictionary(Of String, String)) As Boolean Implements IBusiness.uLMSPartPlanIBusiness.UpdateUseComplete
        Dim strsql As String = String.Format(" update enginepartplan ep set ep.usecompleted='1',ep.finishdate=sysdate where ep.containerid='{0}' or ep.enginecontainerid='{0}' ", para("ContainerId"))
        Dim row As Integer = DatabaseHelper.ExecuteSql(strsql)
        If row > 0 Then
            'Modify by Yuxj20181204增加对大阶段自动完工的处理
            strsql = String.Format("SELECT e.mfgorderid,e.containerno,e.workflowstepid,w.SEQUENCE,w.WORKFLOWSTEPNAME FROM enginepartplan e LEFT JOIN WORKFLOWSTEP w ON w.WORKFLOWSTEPID =e.WORKFLOWSTEPID  WHERE e.containerid='{0}'  or e.enginecontainerid='{0}' ", para("ContainerId")) 'modify by longb 2022-08-15
            Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql)
            If dt.Rows.Count > 0 Then
                Dim ContainerNO As String = dt.Rows(0)("containerno").ToString
                Dim mfgorderid As String = dt.Rows(0)("mfgorderid").ToString
                Dim workflowstepid As String = dt.Rows(0)("workflowstepid").ToString
                Dim workFlowStepName As String = dt.Rows(0)("WORKFLOWSTEPNAME").ToString 'add by longb 2022-08-15
                Dim sequence As Integer = dt.Rows(0)("SEQUENCE").ToString 'add by longb 2022-08-15
                '判断是否有没有完工的任务单
                strsql = String.Format("SELECT * FROM enginepartplan e WHERE e.usecompleted='0' and e.containerno is not null ")
                strsql += String.Format(" and e.workflowstepid='{0}'  and e.mfgorderid='{1}' ", workflowstepid, mfgorderid)
                dt = DatabaseHelper.Query_ReturnDatTable(strsql)
                If dt.Rows.Count = 0 Then
                    '更新大阶段（发动机件号）无任务单的记录
                    strsql = String.Format(" update enginepartplan e set e.usecompleted='1',e.finishdate=sysdate ")
                    strsql += String.Format(" WHERE e.usecompleted='0' and e.containerno is null")
                    strsql += String.Format(" and e.workflowstepid='{0}'  and e.mfgorderid='{1}' ", workflowstepid, mfgorderid)
                    If (DatabaseHelper.ExecuteSql(strsql) > 0) Then 'add by longb 2022-08-15 大阶段（总装、检验试车）自动完工，生成试车通知单、试车返回单
                        Dim noticebill As TestRunBillBusiness = New TestRunBillBusiness()
                        Dim TESTRUNTYPE As String = "0"
                        Dim noticebillpara As New Dictionary(Of String, String)
                        noticebillpara("MFGORDERID") = mfgorderid
                        noticebillpara("CREATEEMPLOYEEID") = para("EmployeeID")
                        noticebillpara("FactoryCode") = para("FactoryCode")
                        If workFlowStepName.IndexOf("总装") >= 0 Then
                            Dim selectStrSql As String = String.Format("SELECT e.*,w.SEQUENCE,w.WORKFLOWSTEPNAME FROM ENGINEPARTPLAN e LEFT JOIN WORKFLOWSTEP w ON w.WORKFLOWSTEPID =e.WORKFLOWSTEPID WHERE CONTAINERNO IS NULL AND MFGORDERID ='{0}' AND w.SEQUENCE='{1}'", mfgorderid, sequence + 1)
                            Dim ddt As DataTable = DatabaseHelper.Query_ReturnDatTable(selectStrSql)
                            If ddt.Rows.Count > 0 And ddt.Rows(0)("WORKFLOWSTEPNAME").ToString.IndexOf("检验试车") >= 0 Then
                                TESTRUNTYPE = "1"
                            End If
                            noticebillpara("TESTRUNTYPE") = TESTRUNTYPE
                            noticebill.AutoInsertNoticeBillData(noticebillpara)
                        End If
                        '大阶段(试车)报工完成，自动生成试车返回单 add by longb 2022-06-16  试车  检验试车
                        If workFlowStepName.IndexOf("试车") >= 0 Then
                            If workFlowStepName.IndexOf("检验试车") >= 0 Then
                                TESTRUNTYPE = "1"
                            End If
                            noticebillpara("TESTRUNTYPE") = TESTRUNTYPE
                            noticebill.AutoInsertRetrunBillData(noticebillpara)
                        End If
                    End If
                End If
            End If

            Return True
        Else
            Return False
        End If
    End Function
#End Region

#Region "Add by Yuxj20180827 发动机进度看板方法"
    ''' <summary>
    ''' Modify by Yuxj20190327
    ''' 增加函数GET_SPLIT_MFGORDER_PRODUCTID查询订单对应ProductId
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetEngineProcessDT(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements IBusiness.uLMSPartPlanIBusiness.GetEngineProcessDT

        Dim sb As New StringBuilder
        Dim strsql As String =
            <string>
                select distinct * from (
                SELECT attributevalue,mfgordername,productname,productid,mfgorderid, to_char(wm_concat(distinct workflowstepname) 
                       over (partition by mfgordername)) wfsList
                FROM (
                    SELECT u.attributevalue,m.mfgordername,pb.productname,p.productid,m.mfgorderid, wfs.workflowstepname FROM mfgorder m
                    LEFT JOIN Ordertype ot ON m.ordertypeid=ot.ordertypeid
                    LEFT JOIN Userattribute u ON u.parentid=m.mfgorderid

                    LEFT JOIN product p ON p.productid=GET_SPLIT_MFGORDER_PRODUCTID(m.mfgorderid)
                    LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid
                   -- LEFT JOIN product p1 ON p1.productid=m.productid
                   -- LEFT JOIN productbase pb ON pb.productbaseid=NVL(p1.productbaseid,m.productbaseid)
                   -- LEFT JOIN product p ON p.productid=NVL(p1.productid,pb.revofrcdid)
                    INNER JOIN workflow wf ON (p.PRODUCTID=wf.PRODUCTID or p.workflowid=wf.workflowid) AND WF.PHASENODE IS NULL
                    LEFT JOIN Workflowbase wfb ON wf.workflowbaseid=wfb.workflowbaseid
                    LEFT JOIN workflowstep wfs ON wf.workflowid=wfs.workflowid                    
                WHERE u.userattributename='发动机'  AND ot.ordertypename not in ('PD05','PD06','PD11','PD13','PD14')
                    and exists (select 1 from enginepartplan where mfgorderid=m.mfgorderid and rownum=1 )          
            </string>
        sb.AppendLine(strsql)
        If para.ContainsKey("MfgOrderName") Then
            sb.AppendFormat(" and m.MfgOrderName like '%{0}%'", para("MfgOrderName"))
        End If

        If para.ContainsKey("EngineNo") Then
            sb.AppendFormat(" and u.attributevalue like '%{0}%'", para("EngineNo"))
        End If

        sb.AppendLine(" ORDER BY m.mfgordername ASC,wfs.sequence ASC ")
        sb.AppendLine(" )  ")
        sb.AppendLine(" )")
        sb.AppendLine(" ORDER BY mfgordername ASC")
        GetEngineProcessDT = New uMESProcessResult
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        GetEngineProcessDT = DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    ''' Add by Yuxj20180829
    ''' 获取发动机各大阶段的生产进度 
    ''' 增加workflowstepid Modify by Yuxj20190301
    ''' </summary>
    ''' <param name="mfgId"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetEngineStatus(ByVal mfgId As String) As DataTable Implements IBusiness.uLMSPartPlanIBusiness.GetEngineStatus
        Dim sb As New StringBuilder
        sb.AppendLine(" SELECT mfgorderid,workflowstepname,workflowstepid, to_char(wm_concat(DISTINCT usecompleted)) status ")
        sb.AppendLine(" FROM ( ")
        sb.AppendLine(" SELECT ep.mfgorderid,ep.workflowstepid,wfs.workflowstepname,ep.usecompleted ")
        sb.AppendLine(" FROM enginepartplan ep  ")
        sb.AppendLine(" LEFT JOIN workflowstep wfs ON ep.workflowstepid=wfs.workflowstepid ")
        sb.AppendFormat(" WHERE ep.mfgorderid='{0}' ", mfgId)
        sb.AppendLine(" )  GROUP BY mfgorderid,workflowstepname,workflowstepid ")
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Return dt
    End Function

    ''' <summary>
    ''' 判断某大阶段订单是否有派工记录
    ''' </summary>
    ''' <param name="mfgId"></param>
    ''' <param name="wfsid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetEngineStepStatus(ByVal mfgId As String, ByVal wfsid As String) As Boolean Implements IBusiness.uLMSPartPlanIBusiness.GetEngineStepStatus
        Dim sb As New StringBuilder
        sb.AppendLine(" SELECT 1 FROM enginepartplan ep ")
        sb.AppendLine(" INNER JOIN containerstepdispatch csd ON nvl(ep.containerid,ep.enginecontainerid)=csd.containerid ")
        sb.AppendFormat(" WHERE ep.mfgorderid='{0}' AND ep.workflowstepid='{1}' and csd.isuse=0 ", mfgId, wfsid)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        If dt.Rows.Count > 0 Then
            GetEngineStepStatus = True
        Else
            GetEngineStepStatus = False
        End If
    End Function

    ''' <summary>
    ''' Add by Yuxj20180829
    ''' 获取发动机任务单的信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetContainerNoStatus(ByVal para As Dictionary(Of String, String)) As DataTable Implements IBusiness.uLMSPartPlanIBusiness.GetContainerNoStatus
        Dim strsql As String =
            <string>
                SELECT u.attributevalue, ep.mfgorderid,m.mfgordername,ep.containerno,ep.containerId,pb.productname,p.description,
                 wfs.workflowstepname,wfb.workflowname||':'||wf.workflowrevision workflowname,wf.description wfdesc,
                 ep.usecompleted,decode(ep.usecompleted,'0'，'未完成','1','完成',NULL) status,decode(csd.containerid,null,'0','1') isDispatch
                FROM enginepartplan ep 
                LEFT JOIN workflowstep wfs ON ep.workflowstepid=wfs.workflowstepid
                LEFT JOIN mfgorder m ON ep.mfgorderid=m.mfgorderid
                LEFT JOIN Userattribute u ON m.mfgorderid=u.parentid
                LEFT JOIN workflow wf ON ep.workflowid=wf.workflowid
                LEFT JOIN workflowbase wfb ON wf.workflowbaseid=wfb.workflowbaseid
                LEFT JOIN product p ON p.productid=ep.productid
                LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid
                LEFT JOIN (SELECT DISTINCT containerid FROM containerstepdispatch where isuse=0) csd on csd.containerid=ep.containerid
            </string>
        Dim sb As New StringBuilder
        sb.AppendLine(strsql)
        sb.AppendFormat("where ep.mfgorderid='{0}' AND u.userattributename='发动机' and wfs.workflowstepname='{1}'", para("MfgOrderId"), para("WorkFlowStepName"))
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        'Dim paraPage As New uMESPagingDataDTO
        'paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        'paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        'paraPage.strSQL = sb.ToString
        'GetContainerNoStatus = DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    ''' Add by Yuxj20181113
    ''' 获取完工工序列表
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetCompleteStep(ByVal pid As String, ByVal wfid As String) As DataTable Implements IBusiness.uLMSPartPlanIBusiness.GetCompleteStep
        Dim strsql As String = " SELECT ep.productid,ep.workflowid,ep.completestepid FROM enginepartplan ep "
        'Modify by Yuxj20190418增加对完工序中是否 完工序属于本工艺的判断
        strsql += String.Format(" INNER JOIN workflowstep wfs ON ep.workflowid=wfs.workflowid AND wfs.workflowstepid=ep.completestepid ")
        strsql += String.Format(" WHERE ep.productid='{0}' AND ep.workflowid='{1}' AND ep.completestepid IS NOT NULL ", pid, wfid)
        strsql += "  ORDER BY ep.createdate DESC "
        Return DatabaseHelper.Query_ReturnDatTable(strsql)
    End Function

    Function GetCompleteStepID(ByVal pid As String, ByVal wfid As String) As String Implements IBusiness.uLMSPartPlanIBusiness.GetCompleteStepID
        Dim strsql As String = String.Format(<string>SELECT ep.* FROM (SELECT ep.productid,ep.workflowid,ep.completestepid,
        ROW_NUMBER() OVER(PARTITION BY ep.productid,ep.workflowid,ep.completestepid ORDER BY ep.createdate DESC ) rn
        FROM enginepartplan ep 
        WHERE ep.productid='{0}' AND ep.workflowid='{1}' AND ep.completestepid >'0' )ep
        INNER JOIN workflowstep wfs ON ep.workflowid=wfs.workflowid AND wfs.workflowstepid=ep.completestepid 
        WHERE ep.rn=1</string>.Value, pid, wfid)

        Dim dt = DatabaseHelper.Query_ReturnDatTable(strsql)
        If dt.Rows.Count = 0 Then
            Return String.Empty
        Else
            Return dt.Rows(0)("completestepid").ToString
        End If
    End Function
    ''' <summary>
    ''' 判断订单分解后记录是否包含所有大阶段
    ''' </summary>
    ''' <param name="pid"></param>
    ''' <param name="wfid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetSplitData(ByVal mfgid As String, ByVal pid As String, ByVal wfid As String) As Boolean Implements IBusiness.uLMSPartPlanIBusiness.GetSplitData
        GetSplitData = False
        Dim sb As New StringBuilder
        sb.AppendFormat("SELECT * FROM enginepartplan ep WHERE ep.mfgorderid='{0}'  AND ep.workflowstepid IS NOT NULL ", mfgid)
        sb.AppendLine(" AND NVL(ep.containerid,ep.enginecontainerid) IS NOT NULL ")
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        If dt.Rows.Count > 0 Then
            Exit Function
        End If
        sb = New StringBuilder
        sb.AppendLine("SELECT wfs.workflowstepid,wfs.workflowstepname,ep.mfgorderid FROM workflowstep wfs ")
        sb.AppendFormat("LEFT JOIN enginepartplan ep ON wfs.workflowstepid=ep.workflowstepid AND ep.mfgorderid='{0}' ", mfgid)
        sb.AppendFormat("AND ep.productid='{0}'  ", pid)
        sb.AppendFormat("WHERE ep.workflowstepid IS NULL and wfs.workflowid='{0}' ", wfid)
        dt = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        If dt.Rows.Count > 0 Then
            GetSplitData = True
        End If
    End Function
#End Region

    ''' <summary>
    ''' 获取同发动机件号，同部件，同工艺最近派工的工区
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetNearestWorkCenter(ByVal para As Dictionary(Of String, Object)) As String Implements IBusiness.uLMSPartPlanIBusiness.GetNearestWorkCenter
        Dim sb As New StringBuilder
        sb.AppendFormat(<string> SELECT * FROM (SELECT ep.workflowid,ep.productid,ep.workcenterid,
        ROW_NUMBER() OVER(PARTITION BY ep.workflowid,ep.productid,ep.workcenterid ORDER BY ep.workcenterdate DESC)rn
        FROM enginepartplan ep  
        WHERE ep.productid='{0}' and ep.workflowid='{1}' AND  ep.workcenterid>'0'
        AND get_split_mfgorder_productid(ep.mfgorderid)='{2}') where rn=1</string>.Value, para("ProductID"), para("WorkflowID"), para("mProductID"))
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        If dt.Rows.Count = 0 Then
            Return String.Empty
        Else
            Return dt.Rows(0)("workcenterid")
        End If
    End Function

    ''' <summary>
    ''' 存入大阶段的完工序
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateBigEPComplete(ByVal para As Dictionary(Of String, String)) As Boolean Implements IBusiness.uLMSPartPlanIBusiness.UpdateBigEPComplete
        UpdateBigEPComplete = False

        Dim strSql As String = String.Format(<string>
                            UPDATE enginepartplan ep  SET ep.completestepid='{0}' WHERE ep.enginepartplanid IN
                            (SELECT ep.enginepartplanid FROM enginepartplan ep 
LEFT JOIN workflowstep ws ON ws.workflowstepid=ep.workflowstepid
WHERE  ep.productid='{1}' AND ws.workflowid='{2}')
                               </string>.Value, para("CompletestepID"), para("ProductID"), para("WorkflowID"))

        If DatabaseHelper.ExecuteSql(strSql) > 0 Then
            UpdateBigEPComplete = True
        End If

    End Function

    ''' <summary>
    ''' yuxj20221126记录订单分解时的发动机件号信息
    ''' </summary>
    ''' <param name="dr"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function InsertOrReplaceMfgEngine(ByVal dr As DataRow) As Boolean Implements IBusiness.uLMSPartPlanIBusiness.InsertOrReplaceMfgEngine
        Dim mfgorderid As String = dr("mfgorderid").ToString
        Dim strsql As String = String.Format("SELECT * FROM mfg_engine_data me where me.mfgorderid='{0}'  ", mfgorderid)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql)
        If (dt.Rows.Count = 0) Then
            strsql =
                <string>
                        insert into mfg_engine_data (MFGORDERID, ENGINEPRODUCTID, PRODUCTFAMILYID, PRODUCTFAMILYNAME, ENGINEWORKFLOWID, ENGINEPRODUCTNAME, ENGINEPRODUCTDESC, ENGINEPRODUCTVER)
                        values ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}')
                    </string>

            strsql = String.Format(strsql, dr("MFGORDERID").ToString, dr("PRODUCTID").ToString,
                                   dr("PRODUCTFAMILYID").ToString, dr("PRODUCTFAMILYNAME").ToString, dr("WORKFLOWID").ToString,
                                   dr("PRODUCTNAME").ToString, dr("ProductDescription").ToString, dr("productrevision").ToString)
            DatabaseHelper.ExecuteSql(strsql)
        Else
            '如果先分解的是施工单，该订单已经有记录，执行更新
            If (IsDBNull(dt.Rows(0)("ENGINEWORKFLOWID"))) Then
                DatabaseHelper.ExecuteSql(String.Format("update mfg_engine_data set ENGINEWORKFLOWID='{0}' where mfgorderid='{1}'", dr("WORKFLOWID").ToString, dr("MFGORDERID").ToString))
            End If

        End If
        Return True
    End Function
End Class
